use AdventureWorks
go

--var example
DECLARE @LastName varchar(75)
SET @LastName = 'Abel'
SELECT * 
FROM Person.Contact 
WHERE LastName = @LastName
go

--execute a string as sql with EXEC (expects an textual sql string)
DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @LastName varchar(75)
SET @columnList = 'contactId, NameStyle, Title'
SET @LastName = 'Abel'--single quotes => error
SET @sqlCommand = 'SELECT ' + @columnList + 
	' FROM Person.Contact 
	WHERE LastName = ' + @LastName
print @sqlCommand
SET @columnList = 'contactId, NameStyle, Title'
SET @LastName = '''Abel'''--double quote because of the string replace
SET @sqlCommand = 'SELECT ' + @columnList + 
	' FROM Person.Contact 
	WHERE LastName = ' + @LastName
print @sqlCommand
EXEC (@sqlCommand)
go


--execute a string with store procedure sp_executesql. Advantege: you can use parameters
DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @lstNm varchar(75)
declare @paramsDefinition nvarchar(75)
SET @columnList = 'contactId, NameStyle, Title'
SET @lstNm = 'Abel'--single quotes => because uses parameteres, it doesn't required inline replacement
SET @sqlCommand = 'SELECT ' + @columnList + 
	' FROM Person.Contact 
	WHERE LastName = @LastName;'
print @sqlCommand
set @paramsDefinition = N'@LastName nvarchar(75)';--string with parame definition
EXECUTE sp_executesql @sqlCommand, @paramsDefinition, @LastName = @lstNm
go